package com.yihu.wlyy.service.app.talk;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.transaction.Transactional;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.util.StringUtil;
import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.yihu.wlyy.entity.WlyyTalkGroup;
import com.yihu.wlyy.entity.WlyyTalkGroupMember;
import com.yihu.wlyy.repository.TalkGroupDao;
import com.yihu.wlyy.repository.TalkGroupMemberDao;
import com.yihu.wlyy.service.BaseService;

@Service
@Transactional
public class TalkGroupService extends BaseService {

    // 讨论组DAO
    @Autowired
    TalkGroupDao talkGroupDao;
    @Autowired
    // 讨论组成员DAO
    TalkGroupMemberDao talkGroupMemberDao;
    @Autowired
    JdbcTemplate jdbcTemplate;

    /**
     * 讨论组保存
     *
     * @param talkGroup
     * @return
     */
    public void saveTalkGroup(WlyyTalkGroup talkGroup, List<WlyyTalkGroupMember> members) {
        talkGroupDao.save(talkGroup);
        talkGroupMemberDao.save(members);
    }

    /**
     * 保存成员
     *
     * @param members
     */
    public void saveMembers(List<WlyyTalkGroupMember> members){
        talkGroupMemberDao.save(members);
    }

    /**
     * 讨论组成员添加
     *
     * @param member
     */
    public void saveTalkMember(WlyyTalkGroupMember member) {
        talkGroupMemberDao.save(member);
    }

    /**
     * 移除讨论组成员
     *
     * @param groupCode  讨论组标识
     * @param memberCode 成员标识
     */
    public void deleteTalkMember(String groupCode, String memberCode) {
        WlyyTalkGroupMember member = talkGroupMemberDao.findByGroupCodeAndMemberCode(groupCode, memberCode);
        member.setStatus(2);
    }

    /**
     * 创建咨询对应的讨论组
     *
     * @param doctor
     * @param doctorName
     * @param patient
     * @param patientName
     */
    public void createConsultTalkGroup(String doctor, String doctorName, String hospitalCode, String hospitalName,
                                       String patient, String patientName, String consult) {
        //讨论组建立
        WlyyTalkGroup talkGroup = new WlyyTalkGroup();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyy-MM-dd");
        String name = patientName + "—咨询(" + dateFormat.format(new Date()) + ")";

        talkGroup.setCode(getCode());
        talkGroup.setName(name);
        talkGroup.setType(1);
        talkGroup.setCreator(patient);
        talkGroup.setCreatorName(patientName);
        talkGroup.setPatient(patient);
        talkGroup.setPatientName(patientName);
        talkGroup.setHospitalCode(hospitalCode);
        talkGroup.setHospitalName(hospitalName);
        talkGroup.setConsultCode(consult);
        talkGroup.setCzrq(new Date());

        List<WlyyTalkGroupMember> members = new ArrayList<WlyyTalkGroupMember>();
        WlyyTalkGroupMember patientMember = new WlyyTalkGroupMember();

        patientMember.setMemberCode(patient);
        patientMember.setMemberName(patientName);
        patientMember.setGroupCode(talkGroup.getCode());
        patientMember.setGroupName(name);
        patientMember.setType(4);
        patientMember.setStatus(1);
        patientMember.setCzrq(new Date());

        members.add(patientMember);

        //当前医生加入讨论组
        WlyyTalkGroupMember doctorMember = new WlyyTalkGroupMember();

        doctorMember.setMemberCode(doctor);
        doctorMember.setMemberName(doctorName);
        doctorMember.setGroupCode(talkGroup.getCode());
        doctorMember.setGroupName(name);
        doctorMember.setType(3);
        doctorMember.setStatus(1);
        doctorMember.setCzrq(new Date());
        members.add(doctorMember);

        saveTalkGroup(talkGroup, members);
    }


    /**
     * 获取医生讨论组列表
     *
     * @param doctor
     * @return
     */
    public List<WlyyTalkGroup> findDoctorTalkGroups(String doctor) {
        return talkGroupDao.findDoctorTalkGroups(doctor);
    }

    /**
     * 根据医生姓名搜索讨论组
     *
     * @param doctorName
     * @return
     */
    public List<WlyyTalkGroup> searchTalkGroupsByDoctorName(String doctor, String doctorName) {
        String doctorNameCon = "%" + doctorName + "%";
        List<String> argStrings = new ArrayList<String>();
        List<WlyyTalkGroup> groups = talkGroupDao.findDoctorTalkGroups(doctor);
        //查询参数
        argStrings.add(doctorNameCon);
        argStrings.add(doctor);
        //查询语句
        String sb = "select DISTINCT tg.* from wlyy_talk_group tg,wlyy_talk_group_member tgm " +
                " where tg.code = tgm.group_code and tgm.member_name like ? and tg.code in ( select group_code from " +
                " wlyy_talk_group_member where member_code = ?)";
        List<WlyyTalkGroup> result = jdbcTemplate.query(sb, argStrings.toArray(), new RowMapper<WlyyTalkGroup>() {
            @Override
            public WlyyTalkGroup mapRow(ResultSet rs, int rowNum) throws SQLException {
                WlyyTalkGroup group = new WlyyTalkGroup();

                group.setId(rs.getLong("id"));
                group.setCode(rs.getString("code"));
                group.setName(rs.getString("name"));
                group.setType(rs.getInt("type"));
                group.setPatient(rs.getString("patient"));
                group.setPatientName(rs.getString("patient_name"));
                group.setConsultCode(rs.getString("consult_code"));
                group.setHospitalCode(rs.getString("hospital_code"));
                group.setHospitalName(rs.getString("hospital_name"));
                group.setCreator(rs.getString("creator"));
                group.setCreatorName(rs.getString("creator_name"));
                group.setCzrq(rs.getDate("czrq"));

                return group;
            }
        });

        if (result == null) {
            return new ArrayList<WlyyTalkGroup>();
        } else {
            return result;
        }
    }

    /**
     * 查询讨论组签约量
     *
     * @param groups
     * @return
     */
    public List<Map<String,Object>> getTalkGroupsSing(String[] groups){
        String params =  "";
        List<String> paramList = new ArrayList<>();

        for(int i = 0; i < groups.length ; i++){
            params += (i == 0?"?":",?");
        }
        paramList.addAll(Arrays.asList(groups));
        paramList.addAll(Arrays.asList(groups));

        String sqlgp = "select group_code,member_code from wlyy_talk_group_member where group_code in (" + params + ") and type = 2";
        String sqlgpm = "select member_code from wlyy_talk_group_member where group_code in (" + params + ") and type = 2";
        String sqlQu = "select * from wlyy_quota_result where qkdoctor_code in (" + sqlgpm+ ") and quato_code = '1' and level1_type = '1'";
        String sqlQuSum = "select a.group_code,sum(ifnull(b.result,0)) sign from ("+ sqlgp +") a left join (" + sqlQu+ ") b on a.member_code = b.qkdoctor_code group by a.group_code ";

        return jdbcTemplate.queryForList(sqlQuSum,paramList.toArray());
    }

    /**
     * 查询对应讨论组成员列表
     *
     * @param groupCode
     * @return
     */
    public List<WlyyTalkGroupMember> findTalkGroupMembers(String groupCode) {
        return talkGroupMemberDao.findByGroupCode(groupCode);
    }

    /**
     * 查找讨论组
     *
     * @param groupCode
     * @return
     */
    public WlyyTalkGroup findTalkGroup(String groupCode){
        return talkGroupDao.findByCode(groupCode);
    }

    /**
     * 查询咨询对应讨论组
     *
     * @param consult
     * @return
     * @throws Exception
     */
    public JSONObject findConsultTalkGroup(String consult) throws Exception {
        WlyyTalkGroup talkGroup = talkGroupDao.findByConsultCode(consult);

        if (talkGroup != null) {
            JSONObject result = new JSONObject(talkGroup);
            List<WlyyTalkGroupMember> members = talkGroupMemberDao.findByGroupCode(talkGroup.getCode());

            if (members != null) {
                result.put("members", members);
            } else {
                result.put("members", "");
            }

            return result;
        } else {
            throw new Exception("没有对应咨询讨论组");
        }
    }

    /**
     * 获取医生对应病人的讨论组纪录
     *
     * @param doctor
     * @param patient
     * @return
     */
    public JSONArray getTalkGroupHistories(String doctor, String patient) {
        List<WlyyTalkGroup> groups = talkGroupDao.findByDoctorAndPatient(doctor, patient);

        return new JSONArray(groups);
    }

    /**
     * 获取两个成员同在的讨论组
     *
     * @param memberFirst
     * @param memberSencond
     * @return
     */
    public List<WlyyTalkGroup> getTalkGroupRelated(String memberFirst, String memberSencond) {
        List<WlyyTalkGroup> result = new ArrayList<>();
        //查询语句
        String sqlIn = " select tg.* from wlyy_talk_group tg where tg.code in ("
                     + " select tgm.group_code "
                     + " from "
                     + "     (select * from wlyy_talk_group_member where member_code = ?) tgm "
                     + "  inner join "
                     + "     (select * from wlyy_talk_group_member where member_code = ?) tgmm "
                     + "  on tgm.group_code = tgmm.group_code )";
        //查询
        result = jdbcTemplate.query(sqlIn, new Object[]{memberFirst, memberSencond}, new RowMapper<WlyyTalkGroup>() {
            @Override
            public WlyyTalkGroup mapRow(ResultSet rs, int rowNum) throws SQLException {
                WlyyTalkGroup group = new WlyyTalkGroup();

                group.setId(rs.getLong("id"));
                group.setCode(rs.getString("code"));
                group.setName(rs.getString("name"));
                group.setType(rs.getInt("type"));
                group.setPatient(rs.getString("patient"));
                group.setPatientName(rs.getString("patient_name"));
                group.setConsultCode(rs.getString("consult_code"));
                group.setHospitalCode(rs.getString("hospital_code"));
                group.setHospitalName(rs.getString("hospital_name"));
                group.setCreator(rs.getString("creator"));
                group.setCreatorName(rs.getString("creator_name"));
                group.setCzrq(rs.getDate("czrq"));

                return group;
            }
        });
        return result;
    }

    /**
     * 获取医生的讨论组统计个数
     *
     * @param doctor
     * @return
     */
    public long getDoctorTalkGroupCount(String doctor){
        return talkGroupMemberDao.countByMemberCode(doctor);
    }
}

